<?php

namespace maotool\office\xls;

use maotool\exception\RuntimeException;
use maotool\file\FileUtil;
use maotool\object\ReflectionUtil;
use maotool\object\StrUtil;
use maotool\util\ArrayUtil;
use maotool\util\TreeUtil;

class Xls
{

    /**
     * 名称
     * @var
     */
    protected $name;

    /**
     * 标题
     * @var
     */
    protected $title;

    /**
     * 密码
     * @var
     */
    protected $password;

    /**
     * @var Sheet[]
     */
    protected $sheets = [];

    public function __construct()
    {

    }

    /**
     * @return Sheet[]
     */
    public function getSheets()
    {
        return $this->sheets;
    }

    /**
     * @param Sheet[] $sheets
     */
    public function setSheets($sheets)
    {
        $this->sheets = $sheets;
    }


    /**
     * @return mixed
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param mixed $name
     */
    public function setName($name)
    {
        $this->name = $name;
    }

    /**
     * @return mixed
     */
    public function getTitle()
    {
        return $this->title;
    }

    /**
     * @param mixed $title
     */
    public function setTitle($title)
    {
        $this->title = $title;
    }

    /**
     * @return mixed
     */
    public function getPassword()
    {
        return $this->password;
    }

    /**
     * @param mixed $password
     */
    public function setPassword($password)
    {
        $this->password = $password;
    }

    /**
     * 获取表格数据为Spreadsheet
     * @return mixed
     */
    public function getSpreadsheet()
    {
        if (ReflectionUtil::notExists('\PhpOffice\PhpSpreadsheet\Spreadsheet')) {
            throw new RuntimeException("导出表格依赖 \PhpOffice\PhpSpreadsheet\Spreadsheet 请先安装扩展");
        }
        $spreadsheet = ReflectionUtil::instance("\PhpOffice\PhpSpreadsheet\Spreadsheet");
        if (StrUtil::hasNotEmpty($this->getPassword())) {
            @$spreadsheet->getActiveSheet()->getProtection()->setSheet(true);
            $spreadsheet->getSecurity()->setLockWindows(true);
            $spreadsheet->getSecurity()->setLockStructure(true);
            $spreadsheet->getSecurity()->setLockRevision(true);
            $spreadsheet->getSecurity()->setRevisionsPassword($this->getPassword());
            $spreadsheet->getSecurity()->setWorkbookPassword($this->getPassword());
        }
        $sheetIndex = 1;
        foreach ($this->sheets as $sheetItem) {
            if ($sheetIndex == 1) {
                $sheet = $spreadsheet->getActiveSheet();
            } else {
                $sheet = $spreadsheet->createSheet();
            }
            if (StrUtil::hasNotEmpty($this->getPassword())) {
                $sheet->getProtection()->setSheet(true);
                $sheet->getProtection()->setSort(false);
                $sheet->getProtection()->setInsertRows(false);
                $sheet->getProtection()->setFormatCells(false);
                $sheet->getProtection()->setPassword($this->getPassword());
            }
            $sheet->setTitle($sheetItem->title ?: 'Sheet' . $sheetIndex);
            $sheet->getDefaultColumnDimension()->setWidth(20);
            $sheet->getDefaultRowDimension()->setRowHeight(22);
            $headerIndex = 1;
            foreach ($sheetItem->getHeader() as $headerItem) {
                $sheet->getStyleByColumnAndRow($headerIndex, 1)
                    ->getFont()
                    ->setBold(true);
                $sheet->getStyleByColumnAndRow($headerIndex, 1)->getAlignment()->setWrapText(true);
                $sheet->getStyleByColumnAndRow($headerIndex, 1)->getAlignment()->setVertical(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Alignment", "VERTICAL_CENTER"));
                $sheet->getStyleByColumnAndRow($headerIndex, 1)->getAlignment()->setHorizontal(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Alignment", "HORIZONTAL_CENTER"));
                $sheet->getRowDimension(1)->setRowHeight(23);
                $sheet->getStyleByColumnAndRow($headerIndex, 1)->getBorders()->getAllBorders()
                    ->setBorderStyle(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Border", "BORDER_THIN"));
                if (ReflectionUtil::getClass($headerItem) === "maotool\office\xlsx\Cell") {
                    $sheet->setCellValueByColumnAndRow($headerIndex, 1, $headerItem->getValue());
                    $sheet->getColumnDimensionByColumn($headerIndex)->setWidth(max(strlen($headerItem->getValue()), 20));
                    $sheet->getStyleByColumnAndRow($headerIndex, 1)->getFont()->getColor()->setARGB($headerItem->getFontColor());
                    $sheet->getStyleByColumnAndRow($headerIndex, 1)->getFill()->setFillType(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Fill", "FILL_SOLID"))->getStartColor()
                        ->setARGB($headerItem->getFillColor());
                } else if (ReflectionUtil::getClass($headerItem) === "maotool\office\xls\Cell") {
                    $sheet->setCellValueByColumnAndRow($headerIndex, 1, $headerItem->getValue());
                    $sheet->getColumnDimensionByColumn($headerIndex)->setWidth(max(strlen($headerItem->getValue()), 20));
                    $sheet->getStyleByColumnAndRow($headerIndex, 1)->getFont()->getColor()->setARGB($headerItem->getFontColor());
                    $sheet->getStyleByColumnAndRow($headerIndex, 1)->getFill()->setFillType(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Fill", "FILL_SOLID"))->getStartColor()
                        ->setARGB($headerItem->getFillColor());
                } else {
                    $sheet->setCellValueByColumnAndRow($headerIndex, 1, $headerItem);
                    $sheet->getColumnDimensionByColumn($headerIndex)->setWidth(max(strlen($headerItem), 20));
                    $sheet->getStyleByColumnAndRow($headerIndex, 1)->getFont()->getColor()->setARGB('FFFFFF');
                    $sheet->getStyleByColumnAndRow($headerIndex, 1)->getFill()->setFillType(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Fill", "FILL_SOLID"))->getStartColor()
                        ->setARGB('5184BA');
                }
                if ($headerIndex == 1 && !empty($sheetItem->getTree())) {
                    $headerIndex =  TreeUtil::getMaxDepth($sheetItem->getTree());
                    $sheet->mergeCellsByColumnAndRow(1, 1, $headerIndex, 1);
                }
                ++$headerIndex;
            }
            if (count($sheetItem->getHeader()) > 0) {
                $sheet->freezePane('A2');
                $listIndex = 2;
            } else {
                $listIndex = 1;
            }
            foreach ($sheetItem->getList() as $item) {
                $headerIndex = 1;
                foreach ($sheetItem->getHeader() as $headerKey => $headerItem) {
                    if (ReflectionUtil::getClass(ArrayUtil::get($item, $headerKey, '')) === "maotool\office\xlsx\Cell") {
                        $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFont()->getColor()->setARGB(ArrayUtil::get($item, $headerKey, '')->getFontColor());
                        $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFill()->setFillType(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Fill", "FILL_SOLID"))->getStartColor()
                            ->setARGB(ArrayUtil::get($item, $headerKey, '')->getFillColor());
                        $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit(strval(ArrayUtil::get($item, $headerKey, '')->getValue()),
                            \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                    } else if (ReflectionUtil::getClass(ArrayUtil::get($item, $headerKey, '')) === "maotool\office\xls\Cell") {
                        $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFont()->getColor()->setARGB(ArrayUtil::get($item, $headerKey, '')->getFontColor());
                        $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFill()->setFillType(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Fill", "FILL_SOLID"))->getStartColor()
                            ->setARGB(ArrayUtil::get($item, $headerKey, '')->getFillColor());
                        $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit(strval(ArrayUtil::get($item, $headerKey, '')->getValue()),
                            \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                    } else {
                        $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit(strval(ArrayUtil::get($item, $headerKey, '')),
                            \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                    }
                    $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getAlignment()->setVertical(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Alignment", "VERTICAL_CENTER"));
                    $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getAlignment()->setHorizontal(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Alignment", "HORIZONTAL_CENTER"));
                    $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getBorders()->getAllBorders()
                        ->setBorderStyle(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Border", "BORDER_THIN"));
                    $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getAlignment()->setWrapText(true);
                    $sheet->getRowDimension($listIndex)->setRowHeight(22);
                    ++$headerIndex;
                }
                ++$listIndex;
            }
            TreeUtil::recursionEach($sheetItem->getTree(), function ($item, $node) use (&$sheet, &$sheetItem, &$listIndex) {
                $headerIndex = 1;
                foreach ($sheetItem->getHeader() as $headerKey => $headerItem) {
                    if ($headerIndex == 1) {
                        for ($i = 0; $i < TreeUtil::getMaxDepth($sheetItem->getTree()); $i++) {
                            if ($i == $node['level']) {
                                if (ReflectionUtil::getClass(ArrayUtil::get($item, $headerKey, '')) === "maotool\office\xlsx\Cell") {
                                    $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFont()->getColor()->setARGB(ArrayUtil::get($item, $headerKey, '')->getFontColor());
                                    $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFill()->setFillType(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Fill", "FILL_SOLID"))->getStartColor()
                                        ->setARGB(ArrayUtil::get($item, $headerKey, '')->getFillColor());
                                    $sheet->setCellValueByColumnAndRow($headerIndex, $listIndex, ArrayUtil::get($item, $headerKey, '')->getValue());
                                } else if (ReflectionUtil::getClass(ArrayUtil::get($item, $headerKey, '')) === "maotool\office\xls\Cell") {
                                    $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFont()->getColor()->setARGB(ArrayUtil::get($item, $headerKey, '')->getFontColor());
                                    $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFill()->setFillType(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Fill", "FILL_SOLID"))->getStartColor()
                                        ->setARGB(ArrayUtil::get($item, $headerKey, '')->getFillColor());
                                    $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit(strval(ArrayUtil::get($item, $headerKey, '')->getValue()),
                                        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                                } else {
                                    $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit(strval(ArrayUtil::get($item, $headerKey, '')),
                                        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                                }
                            } else if ($i < $node['level']) {
                                $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit(strval(ArrayUtil::get($node["parents"][$i], $headerKey, '')),
                                    \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                            } else {
                                $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit("",
                                    \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                            }
                            $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getAlignment()->setVertical(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Alignment", "VERTICAL_CENTER"));
                            $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getAlignment()->setHorizontal(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Alignment", "HORIZONTAL_CENTER"));
                            $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getBorders()->getAllBorders()
                                ->setBorderStyle(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Border", "BORDER_THIN"));
                            $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getAlignment()->setWrapText(true);
                            $sheet->getRowDimension($listIndex)->setRowHeight(22);
                            ++$headerIndex;
                        }
                    } else {
                        if (ReflectionUtil::getClass(ArrayUtil::get($item, $headerKey, '')) === "maotool\office\xlsx\Cell") {
                            $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFont()->getColor()->setARGB(ArrayUtil::get($item, $headerKey, '')->getFontColor());
                            $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFill()->setFillType(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Fill", "FILL_SOLID"))->getStartColor()
                                ->setARGB(ArrayUtil::get($item, $headerKey, '')->getFillColor());
                            $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit(strval(ArrayUtil::get($item, $headerKey, '')->getValue()),
                                \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        } else if (ReflectionUtil::getClass(ArrayUtil::get($item, $headerKey, '')) === "maotool\office\xls\Cell") {
                            $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFont()->getColor()->setARGB(ArrayUtil::get($item, $headerKey, '')->getFontColor());
                            $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getFill()->setFillType(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Fill", "FILL_SOLID"))->getStartColor()
                                ->setARGB(ArrayUtil::get($item, $headerKey, '')->getFillColor());
                            $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit(strval(ArrayUtil::get($item, $headerKey, '')->getValue()),
                                \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        } else {
                            $sheet->getCellByColumnAndRow($headerIndex, $listIndex)->setValueExplicit(strval(ArrayUtil::get($item, $headerKey, '')),
                                \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        }
                        $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getAlignment()->setVertical(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Alignment", "VERTICAL_CENTER"));
                        $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getAlignment()->setHorizontal(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Alignment", "HORIZONTAL_CENTER"));
                        $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getBorders()->getAllBorders()
                            ->setBorderStyle(ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\Style\Border", "BORDER_THIN"));
                        $sheet->getStyleByColumnAndRow($headerIndex, $listIndex)->getAlignment()->setWrapText(true);
                        $sheet->getRowDimension($listIndex)->setRowHeight(22);
                        ++$headerIndex;
                    }
                }
                ++$listIndex;
            });
            ++$sheetIndex;
        }
        $spreadsheet->setActiveSheetIndex(0);
        return $spreadsheet;
    }

    /**
     * 下载表格
     * @return void
     */
    public function download()
    {
        try {
            $spreadsheet = $this->getSpreadsheet();
            header('Content-Disposition: attachment;filename="' . $this->name . '.xlsx"');
            $writer = ReflectionUtil::method(["\PhpOffice\PhpSpreadsheet\IOFactory", "createWriter"], [$spreadsheet, ReflectionUtil::getConstant("\PhpOffice\PhpSpreadsheet\IOFactory", "READER_XLSX")]);
            /* @var \PhpOffice\PhpSpreadsheet\Writer\Xlsx $writer */
            @$writer->save('php://output');
            exit;
        } catch (\PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
            throw new RuntimeException("表格下载失败");
        }
    }

    /**
     * 读取表格文件
     * @return void
     */
    public function readerFile($path)
    {
        $path = FileUtil::path($path);
        $reader = ReflectionUtil::instance('\PhpOffice\PhpSpreadsheet\Reader\Xls');
        /* @var \PhpOffice\PhpSpreadsheet\Reader\Xls $reader */
        try {
            $this->sheets = [];
            $spreadsheet = $reader->load($path);
            $worksheets = $spreadsheet->getActiveSheet();
            foreach ($worksheets->getRowIterator() as $row) {
                foreach ($row->getCellIterator() as $cell) {
                    $value = $cell->getValue();
                    var_dump($value);
                }
            }
        } catch (\PhpOffice\PhpSpreadsheet\Reader\Exception $e) {
            throw new RuntimeException("表格读取失败" . $e->getMessage());
        }
    }

    /**
     * 保存为文件
     * @param string $path
     * @return string
     */
    public function writerFile($path)
    {
        try {
            $path = FileUtil::path($path);
            $spreadsheet = $this->getSpreadsheet();
            $writer = ReflectionUtil::instance('\PhpOffice\PhpSpreadsheet\Writer\Xlsx', [$spreadsheet]);
            /* @var \PhpOffice\PhpSpreadsheet\Writer\Xlsx $writer */
            @$writer->save($path);
            return $path;
        } catch (\PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
            throw new RuntimeException("表格下载失败");
        }
    }

}